package database.dbObjects;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Vector;

import main.Main;

public interface IDBObject {
	
	public abstract class DBObject {
		
		protected void executeCreation(String query) throws Exception {
			
			if ((query == null) || (query.isEmpty())) {
				throw (new Exception("Query string can't be null or empty!!!"));
			}
	    	Statement stat = Main.connection.createStatement();
	        stat.executeUpdate(query);
	        stat.close();
		}
		
		public final boolean add(String objectName, 
				Vector<Object> columnItems) throws Exception {
			
			if ((objectName == null) || (columnItems == null)) {
				return false;
			}
			String query = "insert or rollback into " + objectName + " values (";
			
			for (int i = 0; i < columnItems.size() - 1; i++) {
				query += "?, ";
			}
			query += "?);"; // last item
			
			PreparedStatement prep = Main.connection.prepareStatement(query);
			
			for (int i = 0; i < columnItems.size(); i++) {
				
				if (columnItems.elementAt(i) instanceof String) {
					prep.setString(i + 1, (String)columnItems.elementAt(i));
				} else if (columnItems.elementAt(i) instanceof Character) {
					prep.setString(i + 1, ((Character)columnItems.elementAt(i)).toString());
				} else if (columnItems.elementAt(i) instanceof Integer) {
					prep.setInt(i + 1, (Integer)columnItems.elementAt(i));
				} else if (columnItems.elementAt(i) instanceof Boolean) {
					prep.setBoolean(i + 1, (Boolean)columnItems.elementAt(i));
				} else if (columnItems.elementAt(i) == null) {
					prep.setString(i + 1, null);
				} else {
					throw (new Exception("Type not supported! Only String, Character, " +
							"Integer or Boolean allowed."));
				}
			}
			prep.addBatch();
			
			Main.connection.setAutoCommit(false);
			
			prep.executeBatch();
			prep.close();
			
			Main.connection.setAutoCommit(true);
			
			return true;
		}
		
		// Delete met 1 key (String)
		public boolean delete(String objectName, String keyColumn, 
				String key) throws Exception {
			
			if ((objectName == null) || (keyColumn == null) || (key == null)) {
				return false;
			}
			
			String query = "delete from " + objectName + " " +
						   "where ((" + keyColumn + " = ?));";
			
			PreparedStatement prep = Main.connection.prepareStatement(query);
			
			prep.setString(1, key);
			prep.addBatch();
			
			Main.connection.setAutoCommit(false);
			
			prep.executeBatch();
			prep.close();
			
			Main.connection.setAutoCommit(true);
			
			return true;
		}
		
		// Delete met meer dan 1 key
		public final boolean delete(String objectName, Vector<String> keyColumns, 
				Vector<Object> keys) throws Exception {
			
			if ((objectName == null) || (keyColumns == null) || (keys == null)) {
				return false;
			}
			
			String query = "delete from " + objectName + " " +
			  			   "where (";
			
			for(int i = 0; i < keyColumns.size() - 1; i++) {
				query += "(" + keyColumns.elementAt(i) + " = ?) and ";
			}
			query += "(" + keyColumns.elementAt(keyColumns.size() - 1) + " = ?));"; // last key column
			
			PreparedStatement prep = Main.connection.prepareStatement(query);
			
			for (int i = 0; i < keys.size(); i++) {
				
				if (keys.elementAt(i) instanceof String) {
					prep.setString(i + 1, (String)keys.elementAt(i));
				} else if (keys.elementAt(i) instanceof Integer) {
					prep.setInt(i + 1, (Integer)keys.elementAt(i));
				} else {
					throw (new Exception("Type not supported! Only String or " +
							"Integer allowed."));
				}
			}
			prep.addBatch();
			
			Main.connection.setAutoCommit(false);
			
			prep.executeBatch();
			prep.close();
			
			Main.connection.setAutoCommit(true);
			
			return true;
		}
		
		// Update met 1 key (String)
		protected final boolean update(String table, String keyColumn, String key, 
				String column, Object newInfo) throws Exception {
			
			if ((table == null) || (keyColumn == null) || (key == null) || 
					(column == null) || table.isEmpty() || keyColumn.isEmpty() || 
					key.isEmpty() || column.isEmpty()) {
				
				throw (new Exception("Parameters can't be null or empty!"));
			}
			
			String query = "update or rollback " + table + " " +
				  		   "set " + column + " = ?" +
				  		   "where (" + keyColumn + " = ?);";
			
			PreparedStatement prep = Main.connection.prepareStatement(query);
			
			if (newInfo instanceof String) {
				prep.setString(1, (String)newInfo);
			} else if (newInfo instanceof Integer) {
				prep.setInt(1, (Integer)newInfo);
			} else if (newInfo == null) {
				prep.setString(1, null);
			} else {
				throw (new Exception("Type not supported! Only String or " +
						"Integer allowed."));
			}
			prep.setString(2, key);
			prep.addBatch();
			
			Main.connection.setAutoCommit(false);
			
			prep.executeBatch();
			prep.close();
			
			Main.connection.setAutoCommit(true);
	        
			return true;	
		}
		
		// Update met meer dan 1 key
		protected final boolean update(String table, Vector<String> keyColumns, 
				Vector<Object> keys, String updateColumn, Object newInfo) throws Exception {
			
			if ((table == null) || (keyColumns == null) || (keys == null) || 
					(updateColumn == null) || table.isEmpty() || 
					keyColumns.isEmpty() || keys.isEmpty() || updateColumn.isEmpty()) {
				
				throw (new Exception("Parameters can't be null or empty!"));
			}
			
			String query = "update or rollback " + table + " " +
				  		   "set " + updateColumn + " = ? " +
				  		   "where (";
			
			for(int i = 0; i < keyColumns.size() - 1; i++) {
				query += "(" + keyColumns.elementAt(i) + " = ?) and ";
			}
			query += "(" + keyColumns.elementAt(keyColumns.size() - 1) + " = ?));"; // last key column
			
			PreparedStatement prep = Main.connection.prepareStatement(query);
			
			// the new info
			if (newInfo instanceof String) {
				prep.setString(1, (String)newInfo);
			} else if (newInfo instanceof Integer) {
				prep.setInt(1, (Integer)newInfo);
			} else if (newInfo instanceof Boolean) {
				prep.setBoolean(1, (Boolean)newInfo);
			} else if (newInfo == null) {
				prep.setString(1, null);
			} else {
				throw (new Exception("Type not supported! Only String, " +
						"Integer or Boolean allowed."));
			}
			
			// the keys
			for (int i = 1; i < keys.size(); i++) {
				
				if (keys.elementAt(i) instanceof String) {
					prep.setString(i + 1, (String)keys.elementAt(i));
				} else if (keys.elementAt(i) instanceof Integer) {
					prep.setInt(i + 1, (Integer)keys.elementAt(i));
				} else {
					throw (new Exception("Type not supported! Only String or " +
							"Integer allowed."));
				}
			}
			prep.addBatch();
			
			Main.connection.setAutoCommit(false);
			
			prep.executeBatch();
			prep.close();
			
			Main.connection.setAutoCommit(true);
	        
			return true;	
		}
		
	}
	
}
